IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAdvanceFundRequestCommentsByProposalNumber]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAdvanceFundRequestCommentsByProposalNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--GetAdvanceFundRequestCommentsByProposalNumber '2009A050008'

CREATE PROCEDURE [dbo].[GetAdvanceFundRequestCommentsByProposalNumber] 
(  
	@PropNumber	VARCHAR(50)
)  
AS 

SET NOCOUNT ON
BEGIN  
		    
			--return the request detail
			SELECT afc.Comment AS 'Comment',
			adm.LastName + ', ' + adm.FirstName AS 'CommentedBy',
			CONVERT(VARCHAR(19), afc.DateCommented) AS 'CommentedDate',
			afst.[Name] AS 'RequestStatus'			
			FROM  dbo.AdvanceFundRequestComments AS afc	
			INNER JOIN 	dbo.AdvanceFundRequest AS afr ON afc.AdvanceFundRequestId = afr.Id
			INNER JOIN  dbo.AdvanceFundRequestStatusType AS afst ON afc.AdvanceFundRequestStatusTypeId = afst.Id
			INNER JOIN dbo.AdmPerson AS adm ON adm.FwkDomainUserId = afc.CommentedBy 
			WHERE afr.PropNumber = @PropNumber
			ORDER BY afc.DateCommented DESC

		  --PRINT(@OrderBy)

END




GO